package com.microboom.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.Optional;

import lombok.NonNull;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.microboom.bean.po.User;
import org.springframework.util.ObjectUtils;

import javax.annotation.Nonnull;

@Repository
public class UserDao {

	@Autowired
    private JdbcTemplate jdbcTemplate;

	public void insert(@Nonnull String username, @NonNull  String password) throws SQLException {
		jdbcTemplate.update("INSERT INTO exam_user VALUE(NULL, ?, ?)", username, password);
	}
	
	public void delete(@Nonnull Long uid) throws SQLException {
		jdbcTemplate.update("DELETE FROM exam_user WHERE uid = ?", uid);
	}
	
	public void update(@Nonnull Long uid, @Nonnull String newUsername, @Nonnull String newPassword) throws SQLException {
		jdbcTemplate.update("UPDATE exam_user SET username = ?, password = ? WHERE uid = ?", newUsername, newPassword, uid);
	}

	@Nonnull
	public List<User> select() throws SQLException {
		return jdbcTemplate.query("SELECT * FROM exam_user", new BeanPropertyRowMapper<>(User.class));
	}

	@Nonnull
	public Optional<User> select(@Nonnull String username) throws SQLException {
		List<User> list = jdbcTemplate.query("SELECT * FROM exam_user WHERE username = ?",
				new BeanPropertyRowMapper<>(User.class),
				username);
		User user = list.size() == 0 ? null : list.get(0);
		return Optional.ofNullable(user);
	}

	@Nonnull
	public Optional<User> select(@Nonnull String username, @Nonnull String password) throws SQLException {
		final String sql = "SELECT * FROM exam_user WHERE username = ? AND password = ?";
		List<User> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class), username, password);
		User user = list.size() == 0 ? null : list.get(0);
		return Optional.ofNullable(user);
	}

}
